package com.klikmee.sms.chat.web.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;
import com.klikmee.sms.services.chat.model.Agent;
import com.klikmee.sms.services.chat.model.Question;

public class QuestionDAO extends AbstractDAO<Question> {
	@PersistenceContext
	public EntityManager entityManager;
	private DataSource dataSource;

	public QuestionDAO() {
		super(Question.class);
	}

	public List<Question> findUnassigned() {
		@SuppressWarnings("unchecked")
		List<Question> resultList = entityManager.createNamedQuery("Question.findUnassigned").getResultList();
		return resultList;
	}

	public List<com.klikmee.sms.chat.web.dto.Question> findByAgent(Agent agent) throws SQLException {
		ArrayList<com.klikmee.sms.chat.web.dto.Question> result = new ArrayList<com.klikmee.sms.chat.web.dto.Question>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, " +
				"(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, " +
				"(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, " +
				"(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, " +
				"(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered " +
				"FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID " +
				"WHERE qr.AgentID = ? " +
				"AND a.Visable = 1 " +
				"GROUP BY q.QuestionID " +
				"ORDER BY a.DateAnswered DESC " +
				"LIMIT 0, 100";
		
		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement(query);
		statement.setInt(1, agent.getAgentID());
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");
			boolean answerDelivered = resultSet.getBoolean("AnswerDelivered");

			com.klikmee.sms.chat.web.dto.Question q = new com.klikmee.sms.chat.web.dto.Question();
			q.setQuestionID(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerID(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);
			q.setAnswerDelivered(answerDelivered);

			result.add(q);
		}

		return result;
	}

	public List<com.klikmee.sms.chat.web.dto.Question> findAnswered() throws SQLException {
		ArrayList<com.klikmee.sms.chat.web.dto.Question> result = new ArrayList<com.klikmee.sms.chat.web.dto.Question>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, " +
				"(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, " +
				"(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, " +
				"(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, " +
				"(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered " +
				"FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID " +
				"WHERE a.Visable = 1 " +
				"GROUP BY q.QuestionID " +
				"ORDER BY a.DateAnswered DESC " +
				"LIMIT 0, 100";
		Connection connection = this.dataSource.getConnection();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");
			boolean answerDelivered = resultSet.getBoolean("AnswerDelivered");

			com.klikmee.sms.chat.web.dto.Question q = new com.klikmee.sms.chat.web.dto.Question();
			q.setQuestionID(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerID(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);
			q.setAnswerDelivered(answerDelivered);

			result.add(q);
		}

		return result;
	}

	public List<com.klikmee.sms.chat.web.dto.Question> findHistory(String phoneNumber) throws SQLException {
		ArrayList<com.klikmee.sms.chat.web.dto.Question> result = new ArrayList<com.klikmee.sms.chat.web.dto.Question>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, " +
				"(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, " +
				"(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, " +
				"(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered " +
				"FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID " +
				"where q.FromNumber = ? " +
				"AND a.Visable = 1 " +
				"GROUP BY q.QuestionID " +
				"ORDER BY a.DateAnswered DESC " +
				"LIMIT 0, 100";
		
		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement(query);
		statement.setString(1, phoneNumber);
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");

			com.klikmee.sms.chat.web.dto.Question q = new com.klikmee.sms.chat.web.dto.Question();
			q.setQuestionID(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerID(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);

			result.add(q);
		}

		return result;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}}